﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using DAL.Entities;

namespace DAL.Entities_DAL
{
    public class Nhanvien_DAL : BaseDAL, UTL.IBaseDAL
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            //load csdl tu table
            var sql = @"SELECT  *  FROM NHAN_VIEN";
            return ExecuteQuery(sql);
            
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM NHAN_VIEN WHERE MA_NV = '{0}'";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
            
        }

        public bool Insert(object obj)
        {
            var o = (NHAN_VIEN)obj;
            string sql = @"set dateformat dmy; INSERT INTO NHAN_VIEN(MA_NV, HOTEN_NV, TEN_DANG_NHAP, MA_CV, NGAY_SINH, GIOI_TINH, DIA_CHI, DIEN_THOAI) VALUES ('{0}', N'{1}', '{2}', N'{3}', N'{4}', '{5}', N'{6}', '{7}')";
            sql = string.Format(sql, o.MA_NV, o.HOTEN_NV, o.TEN_DANG_NHAP, o.MA_CV, o.NGAY_SINH, o.GIOI_TINH, o.DIA_CHI, o.DIEN_THOAI);
            return ExecuteNonQuery(sql) > 0 ? true : false;
            
        }

        public bool Update(object obj)
        {
            var o = (NHAN_VIEN)obj;
            string sql = @"set dateformat dmy; UPDATE NHAN_VIEN SET HOTEN_NV= N'{1}', TEN_DANG_NHAP='{2}', MA_CV='{3}', NGAY_SINH = '{4}',  GIOI_TINH= N'{5}', DIA_CHI= N'{6}', DIEN_THOAI = '{7}'  WHERE MA_NV = '{0}' ";
            sql = string.Format(sql, o.MA_NV, o.HOTEN_NV, o.TEN_DANG_NHAP, o.MA_CV, o.NGAY_SINH, o.GIOI_TINH, o.DIA_CHI, o.DIEN_THOAI);
            return ExecuteNonQuery(sql) > 0 ? true : false;
            
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        //tim ma lon nhat
        public DataTable Select_max()
        {
            var sql = @"SELECT MAX(MA_NV) as id FROM NHAN_VIEN";
            return ExecuteQuery(sql);
        }

        //load ten dang nhap len combobox
        public DataTable Select_TenDN()
        {
            string sql = @" 
                            SELECT TEN_DANG_NHAP as ten FROM TAI_KHOAN
                            ";
            return ExecuteQuery(sql);
        }

        public DataTable Select_CV()
        {
            string sql = @" 
                            SELECT MA_CV AS id ,TEN_CV as ten FROM chuc_vu
                            ";
            return ExecuteQuery(sql);
        }


        //tru dan tai khoan khi them nhan vien
        public DataTable Select_TenDN_notuse()
        {
            string sql = @" 
                            SELECT a.TEN_DANG_NHAP as ten FROM TAI_KHOAN a where  a.TEN_DANG_NHAP not in (SELECT b.TEN_DANG_NHAP as ten FROM nhan_vien b where  a.ten_dang_nhap = b.ten_dang_nhap)
                            ";
            return ExecuteQuery(sql);
        }


        //dem so nhan vien
        public string Select_count()
        {
            string sql = @"SELECT COUNT(MA_NV) as id FROM NHAN_VIEN";
            var tb = ExecuteQuery(sql);
            return tb.Rows.Count > 0 ? tb.Rows[0]["id"] + "" : "0";
        }
     
        //khai bao sql bay loi tuoi nhap phai > 18 tuoi
        public string nhaptuoi(DateTime tuoinv)
        {
            try
            {
                var sql = @"
                            set dateformat dmy;
                            select (year(getdate()) - year('{0}')) as tuoinv
                            ";
                sql = string.Format(sql, tuoinv);
                DataTable dt = ExecuteQuery(sql);
                return dt.Rows.Count > 0 ? dt.Rows[0]["tuoinv"] + " " : "0";
            }
            catch
            {
                return null;
            }
        }
    }
}
